/*
 * DbInterface.java
 *
 * Created on April 24, 2007, 4:52 PM
 *
 *  Life Logger v1.0
    Copyright (C) 2007  Anand Kishore (anand@semanticvoid.com)
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.
 
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.
 
    You should have received a copy of the GNU General Public License along
    with this program; if not, write to the Free Software Foundation, Inc.,
    51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
 */

package com.semanticvoid.lifelogger.db;

import com.semanticvoid.lifelogger.configuration.Constants;
import com.semanticvoid.lifelogger.entity.TaggedBrowseHistoryEntity;
import com.semanticvoid.lifelogger.entity.BrowseHistoryEntity;
import com.semanticvoid.lifelogger.entity.SearchHistoryEntity;
import com.semanticvoid.lifelogger.utils.DateHelper;
import java.sql.ResultSet;
import java.util.Calendar;
import java.util.HashSet;
import java.util.Set;
import java.util.Vector;

/**
 *  This class is the interface for all db functions. This wrapper contains all the high level functions
 *  which are to be performed on the db.
 * @author anand kishore
 */
public class DbInterface {
    
    /**
     * The instance of the ConnectionManager.
     */
    private static ConnectionMgr db;
    
    static {
        try {
            db  = new ConnectionMgr();
        } catch(Exception e) {
            System.out.println("Failed to connecto to db");
            e.printStackTrace();
            System.exit(1);
        }
    }
    
    /**
     * Adds all the BrowseHistory entities to db.
     * @return true if all the BrowseHistoryEntities are purged to db successfully
     * false if any BrwoseHistroy entitiy fails to purge to db
     * @param bEntities the vector of BrowseHistoryEntities
     */
    public static boolean purgeBrowseEntitiesToDb(Vector<BrowseHistoryEntity> bEntities) {
        boolean retVal = true;
               
        for(BrowseHistoryEntity entity : bEntities) {
            String query = "insert into browse_history values(\"" + entity.getGuid() + "\",\"" + escapeSQLString(entity.getTitle()) + "\",\"" + entity.getLink() + "\",\"" + DateHelper.parse(entity.getPubDate()) + "\", DEFAULT, " + entity.getType() + ",\"" + entity.getSearchContext() + "\",\"null\",\"null\", DEFAULT)";
            
            try {
                if(!db.fireUpdate(query)) {
                    retVal = false;
                }
            } catch(Exception e) {
                // Do nothing
                e.printStackTrace();
            }
        }
        
        return retVal;
    }
    
    /**
     *   Adds all Browse objects which have tags (TaggedBrowseHistoryEntity) to db
     * @param bEntities the vector of TaggedBrowseHistoryEntity
     * @return true if all the BrowseHistoryEntities are purged to db successfully
     * false if any BrowseHistory entity fails to purge to db
     */
    public static boolean purgeTaggedEntitiesToDb(Vector<TaggedBrowseHistoryEntity> bEntities) {
        boolean retVal = true;
        
        for(TaggedBrowseHistoryEntity entity : bEntities) {
            String query = null;
            
            if(entity.getContent().equalsIgnoreCase("")) {
                query = "insert into browse_history values(\"" + entity.getGuid() + "\",\"" + escapeSQLString(entity.getTitle()) + "\",\"" + entity.getLink() + "\",\"" + DateHelper.parse(entity.getPubDate()) + "\", DEFAULT, " + entity.getType() + ",\"" + entity.getSearchContext() + "\",\"null\",\"null\", DEFAULT)";
            } else {
                query = "insert into browse_history values(\"" + entity.getGuid() + "\",\"" + escapeSQLString(entity.getTitle()) + "\",\"" + entity.getLink() + "\",\"" + DateHelper.parse(entity.getPubDate()) + "\", DEFAULT, " + entity.getType() + ",\"" + entity.getSearchContext() + "\",\"" + escapeSQLString(entity.getContent()) + "\",\"" + escapeSQLString(entity.extractTermVectorAsString()) + "\", DEFAULT)";
            }
            
            
            try {
                if(!db.fireUpdate(query)) {
                    retVal = false;
                } else {
                    
                    // Insert the tags
                    StringBuffer tagsBuff = new StringBuffer();
                    
                    // Tags
                    String[] tags = entity.getTags();
                    
                    // Do not insert the tags if the string is blank OR null
                    if(tags != null || !tagsBuff.toString().equals("")) {
                        for(String tag : entity.getTags()) {
                            tagsBuff.append(" " + tag + " ");
                        }
                        
                        query = "insert into tags values (\"" + entity.getGuid() + "\", \"" + escapeSQLString(tagsBuff.toString()) + "\")";
                        
                        if(!db.fireUpdate(query)) {
                            retVal = false;
                        }
                    }
                }
            } catch(Exception e) {
                // Do nothing
                e.printStackTrace();
            }
        }
        
        return retVal;
    }
    
    /**
     * Add all SearchHistory objects to db
     * @param sEntities the list of SearchHistory entities
     * @return true if all SearchHistory entities are purged to db successfully
     * false if any such entity is not purged to db
     */
    public static boolean purgeSearchEntitiesToDb(Vector<SearchHistoryEntity> sEntities) {
        boolean retVal = true;
        
        for(SearchHistoryEntity entity : sEntities) {
            String query = "insert into search_history values(\"" + entity.getGuid() + "\",\"" + escapeSQLString(entity.getKeywords()) + "\",\"" + entity.getLink() + "\",\"" + DateHelper.parse(entity.getPubDate()) + "\")";
            
            try {
                if(!db.fireUpdate(query)) {
                    retVal = false;
                }
            } catch(Exception e) {
                // Do nothing
                e.printStackTrace();
            }
        }
        
        return retVal;
    }
    
    /**
     * Function to escape the SQL characters
     * @param value the string which needs to escaped
     * @return the string which has the sql sensitive characters escaped
     */
    public static String escapeSQLString(final String value) {
        
        // from the MySQL Manual we learn that the followingis thelist of characters
        // which me must esacpe : \'    \"      \\      \%      \_
        // see : http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html
        final String[] charsToEscape = { "'", "\"", "\\\\", "%", "_", "\\n", "\\r" };
        final String[] escapedChars  = { "''", "\"\"", "\\\\\\\\", "\\%",
        "\\_", "\\\\n", "\\\\r" };
        String replaced = value;
        
        for (int i = 0; i < charsToEscape.length; i++) {
            replaced = replaced.replaceAll(charsToEscape[ i ],
                    escapedChars[ i ]);
        }
        
        return replaced;
    }
    
    /**
     *  Function to get browse_history items with NO content
     *  @return the Array of guids of such items
     */
    public static Vector<BrowseHistoryEntity> getListOfBrowseItemsWithNoContent() {
        Vector<BrowseHistoryEntity> list = new Vector<BrowseHistoryEntity>();
        String query = "select guid, link, title from browse_history where content like \"null\" and valid = " + Constants.VALID_BROWSE_ENTITY;
        
        ResultSet rs = db.fire(query);
        
        if(rs != null) {
            try {
                while(rs.next()) {
                    BrowseHistoryEntity bEntity = new BrowseHistoryEntity();
                    bEntity.setGuid(rs.getString(1));
                    bEntity.setLink(rs.getString(2));
                    bEntity.setTitle(rs.getString(3));

                    list.add(bEntity);
                }
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        return list;
    }
    
    
    /**
     *  Function to write the content of the link
     *  @param guid the id of the item
     *  @param content the parsed html content
     *  @return true if the commit suceeds
     *  false otherwise
     */
    public static boolean writeContent(String guid, String content, String termVector) {
        String query = null;
        
        //query = "update browse_history set content = \"" + escapeSQLString(content) + "\" and termvector = \"" + termVector + "\" where guid = \"" + guid + "\"";
        String query_1 = "update browse_history set content = \"" + escapeSQLString(content) + "\" where guid = \"" + guid + "\"";
        String query_2 = "update browse_history set termvector = \"" + escapeSQLString(termVector) + "\" where guid = \"" + guid + "\"";
        
        return (db.fireUpdate(query_1) && db.fireUpdate(query_2));
    }
    
    public static void markInvalid(String guid) {
        String query = null;
        
        query = "update browse_history set valid = \"" + Constants.INVALID_BROWSE_ENTITY + "\" where guid = \"" + guid + "\"";
        
        db.fireUpdate(query);
    }
    
    public static int getCountForItem(int type) {
        String query = null;
        int count = 0;
        
        if(type == Constants.ITEM_TYPE_FOR_BOOKMARK || type == Constants.ITEM_TYPE_FOR_BROWSE || type == Constants.ITEM_TYPE_FOR_READ_BLOG || type == Constants.ITEM_TYPE_FOR_SEARCH_CLICKTHRU) {
            query = "select count(*) from browse_history where valid = " + Constants.VALID_BROWSE_ENTITY + " and type = " + type;
        } else if(type == Constants.ITEM_TYPE_FOR_SEARCH_QUERY) {
            query = "select count(*) from search_history";
        }
               
        ResultSet rs = db.fire(query);
        
        try {
            if(rs.next()) {
                count = rs.getInt(1);
            }
        } catch(Exception e) {
            // Do nothing
        }
        
        return count;
    }
    
    public static int[] getTimelineDistribution(int type, String from, String to) {
         ResultSet rs = null;
         int[] timeLine = new int[24];
         String query = null;
         
         for(int i=0; i<24; i++) {
             
             if(type == Constants.ITEM_TYPE_FOR_BOOKMARK || type == Constants.ITEM_TYPE_FOR_BROWSE || type == Constants.ITEM_TYPE_FOR_READ_BLOG || type == Constants.ITEM_TYPE_FOR_SEARCH_CLICKTHRU) {
                query = "select count(*) from browse_history where type = " + type + " and pubDate >= \"" + from + " 00:00:00\" and pubDate <= \"" + to + " 23:59:59\" and pubDate like \"% " + DateHelper.format(i) + ":%:%\"";
             } else {
                 query = "select count(*) from search_history where pubDate >= \"" + from + " 00:00:00\" and pubDate <= \"" + to + " 23:59:59\" and pubDate like \"% " + DateHelper.format(i) + ":%:%\"";
             }
             
             rs = db.fire(query);
             
             try {
                 if(rs.next()) {
                    timeLine[i] = rs.getInt(1);
                 }
             } catch(Exception e) {
                 e.printStackTrace();
             }
         }
         
         return timeLine;
    }
    
    public static ResultSet fire(String query) {
        return db.fire(query);
    }
    
    public static boolean updateFire(String query) {
        return db.fireUpdate(query);
    }
    
    public static Vector<BrowseHistoryEntity> getBrowseHistoryEntitiesForDateRange(String from, String to) {
        Vector<BrowseHistoryEntity> entites = new Vector<BrowseHistoryEntity>();
        String query = "select * from browse_history where pubDate >= \"" + from + " 00:00:00\" and pubDate <= \"" + to + " 23:59:59\"";
        System.out.println(query);
        
        ResultSet rs = db.fire(query);
        
        try {
            while(rs.next()) {
                String guid = rs.getString("guid");
                String link = rs.getString("link");
                String title = rs.getString("title");
                short type = rs.getShort("type");
                String searchContext = rs.getString("searchContext");
                String content = rs.getString("content");
                String dateString = rs.getString("pubDate");
                Calendar bDate = DateHelper.convertMySQLDateStringToCalendar(dateString);
                String termVector = rs.getString("termvector");
                
                BrowseHistoryEntity bEntity = new BrowseHistoryEntity(guid, link, bDate, title, type, searchContext, content);
                bEntity.setTermVector(termVector);
                entites.add(bEntity);
            }
        } catch(Exception e) {
            e.printStackTrace();
        }
        
        return entites;
    }
    
    public static Vector<String> getEntityGuidsForTag(String tag) {
        ResultSet rs = null;
        Vector<String> guids = new Vector<String>();
        
        String query = "select distinct(t.guid) from tags t, browse_history bh where t.tags like \"% " + tag + " %\" and t.guid = bh.guid and bh.type = " + Constants.ITEM_TYPE_FOR_BOOKMARK;
        
        try {
            rs = db.fire(query);
            
            while(rs.next()) {
                String guid = rs.getString(1);
                guids.add(guid);
            }
        } catch(Exception e) {
            e.printStackTrace();
        }
        
        return guids;
    }
    
    public static String getTermVectorForEntity(String guid) {
        ResultSet rs = null;
        
        String query = "select termvector from browse_history where guid = \"" + guid + "\"";
        
        try {
            rs = db.fire(query);
            
            if(rs.next()) {
                String termVector = rs.getString(1);
                return termVector;
            }
        } catch(Exception e) {
            e.printStackTrace();
        }
        
        return null;
    }
    
    public static Set<String> getAllTagsInTheSystem() {
        ResultSet rs = null;
        Set<String> tags = new HashSet<String>();
        String tagString = null;
        
        String query = "select group_concat(tags) from tags t, browse_history bh where t.guid = bh.guid and bh.type = " + Constants.ITEM_TYPE_FOR_BOOKMARK;
        
        try {
            rs = db.fire(query);
            
            if(rs.next()) {
                tagString = rs.getString(1);
            }
        } catch(Exception e) {
            e.printStackTrace();
            return tags;
        }
        
        String[] tagArray = tagString.split(" ");
        
        for(String tag : tagArray) {
            tags.add(tag.toLowerCase());
        }
        
        return tags;
    }
    
    public static int getNumberOfTaggedEntitiesInTheSystem() {
        ResultSet rs = null;
        int count = 0;
        String query = "select count(*) from tags t, browse_history bh where t.guid = bh.guid and bh.type = " + Constants.ITEM_TYPE_FOR_BOOKMARK;
        
        try {
            rs = db.fire(query);
            
            if(rs.next()) {
                count = rs.getInt(1);
            }
        } catch(Exception e) {
            e.printStackTrace();
        }
               
        return count;
    }
}
